package DBLayer;

import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;
/**
 * DBSalesOrder.java
 * @author Brian Lassen
 * @version 22. Marts 2012
 */
public class DBSalesOrder implements IFDBSalesOrder
{
	private Connection con;
    
    public DBSalesOrder()
    {
        con = DBConnection.getInstance().getDBcon();
    }

	public ArrayList<SalesOrder> getAllSalesOrders(boolean retriveAssociation) {
		return miscWhere("", retriveAssociation);
	}

	public SalesOrder findSalesOrder(String name, boolean retriveAssociation) {
		String wClause = " name = " + name + "";
        return singleWhere(wClause, retriveAssociation);
	}

	public SalesOrder searchSalesOrderId(int id, boolean retriveAssociation) {
		String wClause = "id = " + id + "";
        System.out.println("Search SalesOrder: " + wClause);
        return singleWhere(wClause, retriveAssociation);
	}

	public SalesOrder searchSalesOrderInvoiceno(int invoiceno, boolean retriveAssociation) {
		String wClause = "Invoiceno = " + invoiceno + "";
        System.out.println("Search product: " + wClause);
        return singleWhere(wClause, retriveAssociation);
	}

	public int createSalesOrder(SalesOrder sorder) {
		int nextId = GetMax.getMaxId("Select max(id) from product");
        nextId = nextId + 1;
        System.out.println("next id = " +  nextId);
        
       int rc = -1;
       String query="INSERT INTO Product(id, date, deliveryStatus, deliveryDate, amount, customerid, invoiceno)  VALUES(" +
            nextId  + ",'"  + 
            sorder.getId()  + "'," + 
            sorder.getDate() + "," + 
            sorder.getDeliveryStatus() + "," + 
            sorder.getDeliveryDate() + ",'" + 
            sorder.getAmount() + "'," + 
            sorder.getCustomerid() + "," +
            sorder.getInvoiceno() + ")";
           
      System.out.println("insert : " + query);                          
      try{
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }   
        catch(Exception ex){
            System.out.println("Insert exception in product db: "+ex);
       }
       return(rc);
    }

	public int updateSalesOrder(SalesOrder sorder) {
		SalesOrder sorderObj = sorder;
        int rc=-1;
      
        String query="UPDATE Product SET " +
             "id = " + sorderObj.getId() + ", " +
             "Date =" + sorderObj.getDate() + ", " +
             "Delivery Status =" + sorderObj.getDeliveryStatus() + ", " +
             "Delivery Date =" + sorderObj.getDeliveryDate() + ", " +
             "getAmount =" + sorderObj.getAmount() + ", " +
             "Customer id =" + sorderObj.getCustomerid() + ", " +
             "WHERE id = '" + sorderObj.getCustomerid() + "," +
             "Invoice no =" + sorderObj.getInvoiceno() + ", " +
             "WHERE invoicenoid = '" + sorderObj.getInvoiceno() + "'";
        
        	System.out.println("Update query:" + query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
                      
            stmt.close();
        }
        catch(Exception ex)
        {
            System.out.println("Update exception in product db: "+ex);
        }
        return(rc);
	}

	public int deleteSalesOrder(int id) {
		int rc=-1;
        String query="DELETE FROM SalesOrder WHERE id = " +
                id + "";
                System.out.println(query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception ex)
        {
            System.out.println("Delete exception in product db: "+ex);
        }
        return(rc);
	}
	
	private ArrayList<SalesOrder> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();   
        
        String query =  buildQuery(wClause);
  
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            
            while( results.next() )
            {
                SalesOrder sorderObj = new SalesOrder();
                sorderObj = buildSalesOrder(results);   
                list.add(sorderObj); 
            }
            stmt.close();
        }
        catch(Exception e)
        {
            System.out.println("Query exception - select: "+e);
            e.printStackTrace();
        }
        return list;
    }
	
	private SalesOrder singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		SalesOrder sorderObj = new SalesOrder();
		String query =  buildQuery(wClause);
		System.out.println(query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
	            
			if( results.next() )
			{
				sorderObj = buildSalesOrder(results);
				stmt.close();
			}
	        
		}   
		catch(Exception ex)
		{
			System.out.println("Query exception: "+ex);
	    }
		return sorderObj;
	}

	private String buildQuery(String wClause)
	{
		String query="SELECT * FROM Product";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
	            
		return query;
	}
	
	private SalesOrder buildSalesOrder(ResultSet results)
    {   
      SalesOrder sorderObj = new SalesOrder();
        try
        {
        	sorderObj.setId(results.getInt(1));
            sorderObj.setDate(results.getString(2));
            sorderObj.setDeliveryStatus(results.getString(3));
            sorderObj.setDeliveryDate(results.getString(4));
            sorderObj.setAmount(results.getInt(5));
            sorderObj.setCustomerid(results.getInt(6));
            sorderObj.setInvoiceno(results.getInt(6));
        }
       catch(Exception ex)
       {
           System.out.println("error in building the Product object");
       }
       return sorderObj;
    }
	
}
